﻿CREATE TRIGGER [dbo].[tr_PurchaseBatch_Number]
ON dbo.t_PurchaseBatch
FOR INSERT
AS

IF @@ROWCOUNT = 0 RETURN

IF EXISTS(SELECT * FROM inserted WHERE ISNULL(ContractHolderID ,0) = 0 )
BEGIN ROLLBACK TRAN RAISERROR('Must provide ContractHolder.',16,1) RETURN END

DECLARE @BN int SET @BN = 1

SELECT @BN = ISNULL(MAX(ISNULL(PB_ALL.BatchNumber,0))+1,1)
FROM inserted i INNER JOIN t_PurchaseBatch PB_ALL ON i.ContractHolderID = PB_ALL.ContractHolderID

UPDATE PB SET BatchNumber = @BN
FROM t_PurchaseBatch PB INNER JOIN inserted i ON PB.ID = i.ID
